/*
Copyright 2024 MySQL Parser Project

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package main

import (
	"fmt"
	"log"

	"mysql-parser/sqlparser"
)

func main() {
	fmt.Println("=== DDL语句示例 ===")
	fmt.Println()

	// CREATE TABLE示例
	fmt.Println("=== CREATE TABLE示例 ===")
	createTableQueries := []string{
		`CREATE TABLE users (
			id INT PRIMARY KEY AUTO_INCREMENT,
			name VARCHAR(100) NOT NULL,
			email VARCHAR(255) UNIQUE NOT NULL,
			age INT DEFAULT 0,
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`,

		`CREATE TABLE IF NOT EXISTS products (
			id BIGINT PRIMARY KEY AUTO_INCREMENT,
			name VARCHAR(200) NOT NULL,
			description TEXT,
			price DECIMAL(10,2) NOT NULL,
			category_id INT,
			stock_quantity INT DEFAULT 0,
			is_active BOOLEAN DEFAULT TRUE,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			INDEX idx_category (category_id),
			INDEX idx_price (price),
			FULLTEXT INDEX ft_name_desc (name, description)
		)`,

		`CREATE TEMPORARY TABLE temp_sales (
			product_id INT,
			sales_date DATE,
			quantity INT,
			revenue DECIMAL(12,2)
		)`,

		`CREATE TABLE orders (
			id INT PRIMARY KEY AUTO_INCREMENT,
			user_id INT NOT NULL,
			total DECIMAL(10,2) NOT NULL,
			status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		)`,
	}

	for i, sql := range createTableQueries {
		fmt.Printf("CREATE TABLE示例 %d:\n%s\n", i+1, sql)
		
		// 解析SQL
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		// 提取表名
		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("创建的表: %v\n", tables)

		fmt.Println("---")
		fmt.Println()
	}

	// ALTER TABLE示例
	fmt.Println("=== ALTER TABLE示例 ===")
	alterTableQueries := []string{
		"ALTER TABLE users ADD COLUMN phone VARCHAR(20)",
		"ALTER TABLE users ADD COLUMN address TEXT AFTER email",
		"ALTER TABLE products MODIFY COLUMN price DECIMAL(12,2) NOT NULL",
		"ALTER TABLE products CHANGE COLUMN description product_description TEXT",
		"ALTER TABLE users DROP COLUMN age",
		"ALTER TABLE products ADD INDEX idx_name (name)",
		"ALTER TABLE products DROP INDEX idx_price",
		"ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)",
		"ALTER TABLE orders DROP FOREIGN KEY fk_user",
		"ALTER TABLE users RENAME TO customers",
	}

	for i, sql := range alterTableQueries {
		fmt.Printf("ALTER TABLE示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  修改的表: %v\n", tables)
		fmt.Println()
	}

	// DROP TABLE示例
	fmt.Println("=== DROP TABLE示例 ===")
	dropTableQueries := []string{
		"DROP TABLE temp_sales",
		"DROP TABLE IF EXISTS old_products",
		"DROP TEMPORARY TABLE temp_data",
		"DROP TABLE users, products, orders",
	}

	for i, sql := range dropTableQueries {
		fmt.Printf("DROP TABLE示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  删除的表: %v\n", tables)
		fmt.Println()
	}

	// CREATE INDEX示例
	fmt.Println("=== CREATE INDEX示例 ===")
	createIndexQueries := []string{
		"CREATE INDEX idx_user_email ON users (email)",
		"CREATE UNIQUE INDEX idx_product_sku ON products (sku)",
		"CREATE INDEX idx_order_date ON orders (created_at)",
		"CREATE INDEX idx_user_name_email ON users (name, email)",
		"CREATE FULLTEXT INDEX ft_product_search ON products (name, description)",
	}

	for i, sql := range createIndexQueries {
		fmt.Printf("CREATE INDEX示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  索引所在表: %v\n", tables)
		fmt.Println()
	}

	// DROP INDEX示例
	fmt.Println("=== DROP INDEX示例 ===")
	dropIndexQueries := []string{
		"DROP INDEX idx_user_email ON users",
		"DROP INDEX idx_product_sku ON products",
		"ALTER TABLE orders DROP INDEX idx_order_date",
	}

	for i, sql := range dropIndexQueries {
		fmt.Printf("DROP INDEX示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  索引所在表: %v\n", tables)
		fmt.Println()
	}

	// CREATE VIEW示例
	fmt.Println("=== CREATE VIEW示例 ===")
	createViewQueries := []string{
		`CREATE VIEW user_order_summary AS
		SELECT 
			u.id,
			u.name,
			u.email,
			COUNT(o.id) as order_count,
			SUM(o.total) as total_spent
		FROM users u
		LEFT JOIN orders o ON u.id = o.user_id
		GROUP BY u.id, u.name, u.email`,

		`CREATE OR REPLACE VIEW active_products AS
		SELECT 
			p.*,
			c.name as category_name
		FROM products p
		JOIN categories c ON p.category_id = c.id
		WHERE p.is_active = TRUE AND p.stock_quantity > 0`,

		`CREATE VIEW monthly_sales AS
		SELECT 
			DATE_FORMAT(created_at, '%Y-%m') as month,
			COUNT(*) as order_count,
			SUM(total) as total_revenue
		FROM orders
		GROUP BY DATE_FORMAT(created_at, '%Y-%m')
		ORDER BY month DESC`,
	}

	for i, sql := range createViewQueries {
		fmt.Printf("CREATE VIEW示例 %d:\n%s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("涉及的表: %v\n", tables)
		fmt.Println("---")
		fmt.Println()
	}

	// DROP VIEW示例
	fmt.Println("=== DROP VIEW示例 ===")
	dropViewQueries := []string{
		"DROP VIEW user_order_summary",
		"DROP VIEW IF EXISTS active_products",
		"DROP VIEW monthly_sales, quarterly_sales",
	}

	for i, sql := range dropViewQueries {
		fmt.Printf("DROP VIEW示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  删除的视图: %v\n", tables)
		fmt.Println()
	}

	// TRUNCATE TABLE示例
	fmt.Println("=== TRUNCATE TABLE示例 ===")
	truncateQueries := []string{
		"TRUNCATE TABLE temp_data",
		"TRUNCATE TABLE log_entries",
		"TRUNCATE TABLE session_data",
	}

	for i, sql := range truncateQueries {
		fmt.Printf("TRUNCATE示例 %d: %s\n", i+1, sql)
		
		stmt, err := sqlparser.Parse(sql)
		if err != nil {
			log.Printf("解析错误: %v\n", err)
			continue
		}

		tables := sqlparser.ExtractAllTables(stmt)
		fmt.Printf("  清空的表: %v\n", tables)
		fmt.Println()
	}
}